/* stuinfo.c */
#include "mysql.h" // Correct include path might be <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// Structure corresponding to the table columns
struct student
{
    int id;              // Student ID
    char name[20];       // Name
    char sex[10];        // Gender (increased size slightly)
    char mailbox[40];    // Mailbox address
    char studep[100];    // Department
};

/* Initialize Database Library and Connection Handle */
MYSQL* initial(MYSQL *con) // Should return MYSQL* or take MYSQL**
{
    // Initialize MySQL library (important for multi-threaded apps or embedded server)
    // Passing 0, NULL, NULL is typical for client applications
    if (mysql_library_init(0, NULL, NULL) != 0) { // Check return value
        fprintf(stderr, "Could not initialize MySQL library.\n");
        exit(1); // Exit if library initialization fails
    }

    // Initialize connection handle
    con = mysql_init(NULL);
    if (con == NULL) {
        fprintf(stderr, "mysql_init() failed\n");
        mysql_library_end(); // Cleanup library if init fails
        exit(1);
    }
    return con;
}

/* Connect to the database */
// Changed return type to void, handle connection failure inside
// Pass db name separately or handle NULL db initially
MYSQL* myconnect(MYSQL *con, const char *username, const char *passwd, const char *hostip, const char *db, unsigned int port)
{
    // Attempt to connect
    // Using hardcoded values for simplicity, better to pass them or read from config
    if (mysql_real_connect(con, hostip, username, passwd, db, port, NULL, 0) == NULL) {
        fprintf(stderr, "Connection failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
        mysql_close(con); // Close handle on connection failure
        mysql_library_end();
        exit(1); // Exit on connection failure
    }
    printf("Database connection successful.\n");
    return con; // Return the handle on success
}

/* Create Database */
void createDB(MYSQL *con, const char *cmd) {
    if (mysql_query(con, cmd)) { // Use mysql_query for simplicity here
        fprintf(stderr, "Create Database failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
    } else {
        printf("Create Database command executed successfully (check server status).\n");
        // Note: Success here means the command was sent; actual creation depends on privileges etc.
    }
}

/* Create Table */
// Requires selecting the database first
void createTable(MYSQL *con, const char *db, const char *cmd) {
    // Select the database
    if (mysql_select_db(con, db) != 0) {
        fprintf(stderr, "Select DB '%s' failed: Error %u: %s\n", db, mysql_errno(con), mysql_error(con));
        return; // Don't proceed if DB selection fails
    }
    printf("Database '%s' selected successfully.\n", db);

    // Execute the CREATE TABLE command
    if (mysql_query(con, cmd)) {
        fprintf(stderr, "Create Table failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
    } else {
        printf("Create Table command executed successfully.\n");
    }
}

/* Display Table Contents */
void displayTable(MYSQL *con, const char *query) {
    MYSQL_RES *mysql_res; // Result set pointer
    MYSQL_ROW mysql_row;   // Row data structure
    int i, j;
    my_ulonglong num_row; // Use my_ulonglong for row count
    unsigned int num_col;  // Use unsigned int for column count

    // Execute the query
    if (mysql_real_query(con, query, (unsigned int)strlen(query)) != 0) {
        fprintf(stderr, "Query failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
        return;
    }

    // Store the result set
    mysql_res = mysql_store_result(con);
    if (mysql_res == NULL) {
        // Check if it was an error or a query with no result set (e.g., INSERT)
        if(mysql_field_count(con) == 0) {
             printf("Query executed successfully, no result set returned (e.g., INSERT, UPDATE, DELETE).\n");
             printf("Affected rows: %llu\n", mysql_affected_rows(con));
        } else {
            // An actual error occurred retrieving the result set
            fprintf(stderr, "Store result failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
        }
        return; // Exit function if no result set or error
    }

    // Get number of rows and columns
    num_row = mysql_num_rows(mysql_res);
    num_col = mysql_num_fields(mysql_res);
    printf("Query returned %llu row(s), %u column(s).\n", num_row, num_col);
    printf("*****************************************\n");

    // Fetch and display each row
    for (i = 0; i < num_row; i++) {
        mysql_row = mysql_fetch_row(mysql_res);
        if (mysql_row == NULL) { // Should not happen if num_row is correct, but check anyway
            fprintf(stderr, "Failed to fetch row %d\n", i+1);
            break;
        }

        // Optional: Get column lengths (not used in printf below)
        // unsigned long *lengths = mysql_fetch_lengths(mysql_res);

        // Print each column value in the row
        for (j = 0; j < num_col; j++) {
            printf("%s\t", mysql_row[j] ? mysql_row[j] : "NULL"); // Print NULL if data is NULL
        }
        printf("\n");
    }
    printf("*****************************************\n");

    // Free the result set memory
    mysql_free_result(mysql_res);
}


/* Insert Data */
void insertData(MYSQL *con) {
    struct student student1;
    char query[1024]; // Buffer for SQL query

    // Get student data from user input
    printf("Please input student id: \n");
    // Basic input validation is recommended in real applications
    while(scanf("%d", &student1.id) != 1) {
         printf("Invalid input. Please enter an integer ID: \n");
         while(getchar() != '\n'); // Clear input buffer
    }
    getchar(); // Consume the newline left by scanf

    printf("Please input student name: \n");
    fgets(student1.name, sizeof(student1.name), stdin);
    student1.name[strcspn(student1.name, "\n")] = 0; // Remove trailing newline

    printf("Please input student sex (male/female): \n");
    fgets(student1.sex, sizeof(student1.sex), stdin);
    student1.sex[strcspn(student1.sex, "\n")] = 0;

    printf("Please input student mailbox: \n");
    fgets(student1.mailbox, sizeof(student1.mailbox), stdin);
    student1.mailbox[strcspn(student1.mailbox, "\n")] = 0;

    printf("Please input student department: \n");
    fgets(student1.studep, sizeof(student1.studep), stdin);
    student1.studep[strcspn(student1.studep, "\n")] = 0;


    // Construct the INSERT query - **WARNING: Vulnerable to SQL Injection!**
    // Use prepared statements in real applications for security and efficiency.
    sprintf(query, "INSERT INTO stuinfo(id, stuname, gender, mailbox, studep) VALUES (%d, '%s', '%s', '%s', '%s')",
            student1.id, student1.name, student1.sex, student1.mailbox, student1.studep);
    // Note: String values need to be properly escaped to handle special characters like '
    // mysql_real_escape_string() should be used here for production code.

    printf("Executing query: %s\n", query);

    // Execute the query
    if (mysql_real_query(con, query, (unsigned int)strlen(query)) != 0) {
        fprintf(stderr, "Insert failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
        // Transaction handling (optional, depends on if autocommit is off)
        // mysql_rollback(con); // Rollback if part of a larger transaction
    } else {
        printf("Insert successful. Affected rows: %llu\n", mysql_affected_rows(con));
        // Transaction handling (optional)
        // mysql_commit(con); // Commit if part of a larger transaction
    }

    // Display table contents after insertion
    displayTable(con, "SELECT * FROM stuinfo");
}

/* Delete Data */
void deleteData(MYSQL *con) {
    int id;
    char query[1024];

    printf("Please input student id to be deleted: \n");
    while(scanf("%d", &id) != 1) {
         printf("Invalid input. Please enter an integer ID: \n");
         while(getchar() != '\n'); // Clear input buffer
    }
    getchar(); // Consume the newline

    // Construct the DELETE query
    sprintf(query, "DELETE FROM stuinfo WHERE id = %d", id);
    printf("Executing query: %s\n", query);

    // Execute the query
    if (mysql_real_query(con, query, (unsigned int)strlen(query)) != 0) {
        fprintf(stderr, "Delete failed: Error %u: %s\n", mysql_errno(con), mysql_error(con));
        // mysql_rollback(con); // Optional transaction handling
    } else {
        // Check how many rows were actually deleted
        my_ulonglong affected_rows = mysql_affected_rows(con);
        if (affected_rows > 0) {
            printf("Delete successful. %llu row(s) deleted.\n", affected_rows);
        } else {
            printf("Delete command executed, but no rows matched the ID %d.\n", id);
        }
        // mysql_commit(con); // Optional transaction handling
    }

    // Display table contents after deletion
    displayTable(con, "SELECT * FROM stuinfo");
}


/* Display Menu and Get Choice */
int menu() {
    int choose = -1; // Initialize to an invalid choice
    printf("\n*****************************************\n");
    printf("****** Student Information System *******\n");
    printf("*****************************************\n");
    printf("1. Create Database ('student')\n");
    printf("2. Create Table ('stuinfo')\n");
    printf("3. Insert Data\n");
    printf("4. Delete Data\n");
    printf("5. Display All Data\n"); // Changed from Update to Display
    printf("0. Exit\n");
    printf("*****************************************\n");
    printf("Please input your choice: ");

    // Loop until a valid integer choice is entered
    while (scanf("%d", &choose) != 1) {
        printf("Invalid input. Please enter a number (0-5): ");
        while (getchar() != '\n'); // Clear the invalid input
    }
    getchar(); // Consume the newline character left by scanf

    return choose;
}

/* Handle Menu Choices */
void handleMenu(MYSQL *mysql) {
    int choose;
    const char *db_name = "student"; // Define DB name
    const char *table_sql = "CREATE TABLE stuinfo (id INT PRIMARY KEY, stuname TEXT, gender TEXT, mailbox TEXT, studep TEXT)"; // Added PRIMARY KEY
    const char *db_sql = "CREATE DATABASE student";

    while (1) {
        choose = menu();
        switch (choose) {
            case 1:
                createDB(mysql, db_sql);
                break;
            case 2:
                // Ensure database 'student' is selected before creating table
                createTable(mysql, db_name, table_sql);
                break;
            case 3:
                // Ensure database 'student' is selected
                if (mysql_select_db(mysql, db_name) != 0) {
                     fprintf(stderr,"Cannot select database '%s' for insert. Create/Select it first.\n", db_name);
                } else {
                    insertData(mysql);
                }
                break;
            case 4:
                 // Ensure database 'student' is selected
                if (mysql_select_db(mysql, db_name) != 0) {
                     fprintf(stderr,"Cannot select database '%s' for delete. Create/Select it first.\n", db_name);
                } else {
                    deleteData(mysql);
                }
                break;
            case 5: // Display All Data
                 // Ensure database 'student' is selected
                if (mysql_select_db(mysql, db_name) != 0) {
                     fprintf(stderr,"Cannot select database '%s' for display. Create/Select it first.\n", db_name);
                } else {
                    displayTable(mysql, "SELECT * FROM stuinfo");
                }
                break;
            case 0:
                printf("Exiting program.\n");
                return; // Exit the handleMenu function
            default:
                printf("Invalid choice. Please try again.\n");
                break;
        }
    }
}

/* Main Function */
int main(void) {
    MYSQL *mysql_handle = NULL; // Initialize handle to NULL

    // Initialize library and connection handle
    mysql_handle = initial(mysql_handle); // initial now returns the handle

    // Connect to MySQL server (without specifying a database initially)
    // Replace "root", "root", "localhost" with your actual credentials/host if different
    // Port 0 typically means use the default MySQL port (3306)
    mysql_handle = myconnect(mysql_handle, "root", "root", "localhost", NULL, 0);

    // Handle user interactions through the menu
    handleMenu(mysql_handle);

    // Close the connection
    mysql_close(mysql_handle);
    printf("Database connection closed.\n");

    // Clean up the MySQL library
    mysql_library_end();
    printf("MySQL library ended.\n");

    return 0;
}